﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.SqlClient;
using System.Data;
using System.Collections;
using Microsoft.Win32;
using DatabaseEntegreItri.DataModel;
namespace DatabaseEntegreItri
{
    public class DBFunctions
    {
        public static bool hasConnection(String conString, ref SqlConnection sqlCon)
        {
            try
            {
                sqlCon = new SqlConnection(conString);
                sqlCon.Open();
                sqlCon.Close();
            }
            catch (Exception ex)
            {
                Logs.logList.Add(" : hasConnection : " + ex.Message);
                return false;
            }
            return true;
        }
        public static int getTableRowCount(SqlConnection sqlCon, string tablename)
        {
            try
            {
                SqlCommand sqlCmd = new SqlCommand("select count(*) from " + tablename, sqlCon);
                if (sqlCon.State == ConnectionState.Closed)
                    sqlCon.Open();

                object count = sqlCmd.ExecuteScalar();
                sqlCon.Close();
                return Convert.ToInt32(count);
            }
            catch
            {
                return -1;
            }
        }
        public static DataSet fillDataset(String sqlCommand, SqlConnection sqlConn)
        {            
            SqlCommand cmd = new SqlCommand(sqlCommand, sqlConn);
            SqlDataAdapter adp = new SqlDataAdapter(cmd);
            DataSet ds = new DataSet();

            if (sqlConn.State == ConnectionState.Closed)
            {
                sqlConn.Open();
            }
            adp.Fill(ds);
            sqlConn.Close();
            return ds;
        }

        public static object executeMyScalar(SqlCommand command, SqlConnection connection) {

            if (connection.State == ConnectionState.Closed)
                connection.Open();

            // get id to update related tables.
            object temp = command.ExecuteScalar();
            connection.Close();
            return temp;
        }

        #region DB UTILS

        public static String getSqlServerVersion(SqlConnection sqlCon)
        {
            try
            {
                SqlCommand sqlCmd = new SqlCommand("SELECT @@VERSION", sqlCon);
                if (sqlCon.State == ConnectionState.Closed)
                    sqlCon.Open();

                object count = sqlCmd.ExecuteScalar();
                sqlCon.Close();
                return count.ToString();
            }
            catch
            {
                return "";
            }

        }

        public static String prepareInsertCommand(String sqlCmd, SortedList<String, Type> KeyList)
        {
            foreach (string str in KeyList.Keys)
            {
                sqlCmd += "[" + str + "],";
            }
            sqlCmd = sqlCmd.Remove(sqlCmd.Length - 1);
            sqlCmd += ") VALUES (";

            foreach (string str in KeyList.Keys)
            {
                sqlCmd += "@" + str + ",";
            }
            sqlCmd = sqlCmd.Remove(sqlCmd.Length - 1);
            sqlCmd += ")";
            return sqlCmd;
        }

        // Fills the parameters taken from DataRow and returns id
        public static int mergeDataRowWithParameters(SqlCommand commandInsert, DataRow dr, String idString)
        {
            int ret = -1;
            foreach (DataColumn col in dr.Table.Columns)
            {   // Traverse all columns in a table 
                String caption = col.Caption;
                object columnValue = dr[caption];
                if (caption.Equals(idString))
                {
                    ret = Convert.ToInt32(columnValue);
                }
                if (commandInsert.Parameters.Contains("@" + caption))
                {
                    if (commandInsert.Parameters["@" + caption].Value == DBNull.Value)
                    {   // Eğer değer manuel atanmadı ise
                        commandInsert.Parameters["@" + caption].Value = columnValue;
                    }
                    else
                    { }
                }
            }
            return ret;
        }


        public static SortedList<String, Type> getKeyList(SqlConnection conn, String sqlString, ArrayList excludeList)
        {
            SortedList<String, Type> KeyList = new SortedList<String, Type>();
            SqlCommand cmd = new SqlCommand(sqlString, conn);
            SqlDataAdapter adp = new SqlDataAdapter(cmd);
            DataSet ds = new DataSet();

            if (conn.State == ConnectionState.Closed)
            {
                conn.Open();
            }
            adp.Fill(ds);
            conn.Close();

            foreach (DataColumn col in ds.Tables[0].Columns)
            {
                // Traverse all columns in a table 
                String caption = col.Caption;
                Type tip = col.DataType;
                if (caption != "id" && (excludeList == null || !excludeList.Contains(caption)))
                {
                    KeyList.Add(caption, tip);
                }
            }
            return KeyList;
        }

        private static SqlDbType TypeToDbType(Type tip)
        {
            if (tip == typeof(String))
            {
                return SqlDbType.NVarChar;
            }
            if (tip == typeof(Boolean))
            {
                return SqlDbType.Bit;
            }
            else if (tip == typeof(Int32))
            {
                return SqlDbType.Int;
            }
            else if (tip == typeof(byte[]))
            {
                return SqlDbType.Image;
            }
            else if (tip == typeof(Double))
            {
                return SqlDbType.Float;
            }
            else if (tip == typeof(DateTime))
            {
                return SqlDbType.DateTime;
            }
            else if (tip == typeof(Decimal))
            {

                return SqlDbType.Decimal;
            }
            else
            { }
            return SqlDbType.Image;
        }

        public static void initializeSqlParameters(SqlCommand commandInsert, SortedList<String, Type> KeyList)
        {
            commandInsert.Parameters.Clear();
            foreach (var variable in KeyList)
            {
                SqlDbType dbType = DBFunctions.TypeToDbType(variable.Value);
                commandInsert.Parameters.Add(new SqlParameter("@" + variable.Key, dbType));
                commandInsert.Parameters["@" + variable.Key].Value = DBNull.Value;
            }
        }

        #endregion

        public static int EnumSQLInstances(ref string[] strInstanceArray, ref string[] strEditionArray, ref string[] strVersionArray)
        {
            using (RegistryKey Key = Registry.LocalMachine.OpenSubKey("Software\\Microsoft\\Microsoft SQL Server\\", false))
            {
                if (Key == null) return 0;
                string[] strNames;
                strNames = Key.GetSubKeyNames();

                //If we can not find a SQL Server registry key, we return 0 for none
                if (strNames.Length == 0) return 0;

                //How many instances do we have?
                int iNumberOfInstances = 0;

                foreach (string s in strNames)
                {
                    if (s.StartsWith("MSSQL."))
                        iNumberOfInstances++;
                }

                //Reallocate the string arrays to the new number of instances
                strInstanceArray = new string[iNumberOfInstances];
                strVersionArray = new string[iNumberOfInstances];
                strEditionArray = new string[iNumberOfInstances];
                int iCounter = 0;

                foreach (string s in strNames)
                {
                    if (s.StartsWith("MSSQL."))
                    {
                        //Get Instance name
                        using (RegistryKey KeyInstanceName =
Key.OpenSubKey(s.ToString(), false))
                        {
                            strInstanceArray[iCounter] =
(string)KeyInstanceName.GetValue("");
                        }

                        //Get Edition
                        using (RegistryKey KeySetup =
Key.OpenSubKey(s.ToString() + "\\Setup\\", false))
                        {
                            strEditionArray[iCounter] =
(string)KeySetup.GetValue("Edition");
                            strVersionArray[iCounter] =
(string)KeySetup.GetValue("Version");
                        }

                        iCounter++;
                    }
                }
                return iCounter;
            }
        }

    }
}
